package school.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import school.entity.ExamResult;
import school.util.DBUtil;

public class ExamResultDao {
	public static final ExamResultDao DAO = new ExamResultDao();

	/**
	 * 根据学号查找其考试成绩
	 * 
	 * @param stuno
	 * @return
	 */
	public List<ExamResult> findScores(Long stuno) {
		String sql = "select r.*,s.sname,sb.subjectname from examresult r inner join"
				+ " student s on(s.stuno=r.st_id)"
				+ " inner join `subject` sb on(sb.id=r.sb_id)"
				+ " where r.st_id=? order by exam_date desc";

		// >>>>
		return null;
	}

	// 增加一条考试记录，同时更新对应的学生的最后考试时间
	public void add(ExamResult r) {
		String sql1 = "insert into examresult(st_id,sb_id,exam_date,score)values(?,?,?,?)";
		String sql2 = "update student set last_exam=? where stuno=?";
		Connection con = DBUtil.getConnection();

		PreparedStatement ps = null;
		try {
			con.setAutoCommit(false);// 关闭自动提交特性
			ps = con.prepareStatement(sql2);
			ps.setObject(1, r.getExamDate());
			ps.setLong(2, r.getSt_id());
			int r2 = ps.executeUpdate();

			DBUtil.close(ps);

			ps = con.prepareStatement(sql1);
			ps.setLong(1, r.getSt_id());
			ps.setLong(2, r.getSb_id());
			ps.setObject(3, r.getExamDate());
			ps.setFloat(4, r.getScore());
			int r1 = ps.executeUpdate();

			if (r1 == r2 && r1 == 1) {
				con.commit();
			} else {

				con.rollback();
			}
		} catch (SQLException e) {
			if (con != null)
				try {
					con.rollback();
				} catch (SQLException e1) {
					// TODO 自动生成的 catch 块
					e1.printStackTrace();
				}
			e.printStackTrace();
		} finally {
			DBUtil.close(null, ps, con);
		}

	}
}
